package dao;

import dto.HorarioDTO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.DayOfWeek;
import java.time.LocalTime;
import java.util.ArrayList;

/**
 *
 * @author lucifer
 */
public class HorarioDAO {

  public static final String Tab =
    "create table horario (" +
    "id serial," + //pk
    "id_medico int,"+ //fk
    "dia int," + //1: MONDAY, 2: TUESDAY…
    "hora_desde time," + //08:45, 11:30…
    "hora_hasta time " +
    ");"
  ;

  public static final String Tuples = "";
    /*
    "insert into horario (id_medico, dia, hora_desde, hora_hasta) values" +
    "(1,1,'08:00','12:00')," +
    "(1,5,'12:00','17:35')," +

    "(2,1,'08:45','12:30')," +
    "(2,2,'11:15','19:10')," +
    "(2,4,'12:05','18:21')," +

    "(3,1,'08:00','12:00')," +
    "(3,3,'08:00','11:00')," +
    "(3,6,'09:00','12:00')," +

    "(4,1,'08:00','12:00')," +
    "(4,3,'08:00','12:00')," +
    "(4,5,'08:00','12:00')," +

    "(5,1,'08:00','12:00')," +
    "(5,1,'13:00','17:00')," +

    "(6,1,'08:00','22:00')," +

    "(7,1,'08:00','12:00')," +
    "(7,2,'08:00','12:00')," +
    "(7,3,'08:00','12:00')," +
    "(7,4,'08:00','12:00')," +
    "(7,5,'08:00','12:00')," +
    "(7,6,'08:00','12:00')," +

    "(8,6,'09:00','11:45')," +
    "(8,7,'09:00','11:45')," +

    "(9,1,'08:00','12:00')," +
    "(9,1,'13:15','17:30')," +
    "(9,1,'18:00','22:45')," +

    "(10,1,'18:00','19:00')," +
    "(10,3,'19:00','21:00')," +
    "(10,4,'09:13','17:28')," +

    "(11,1,'01:00','04:00')," +
    "(11,1,'07:00','09:00')," +
    "(11,1,'12:00','15:00')," +

    "(12,1,'09:00','09:20')," +
    "(12,2,'10:20','10:40')," +
    "(12,3,'12:40','13:00');"
  ;
  */

  private static HorarioDAO dao = null;
  private final Connection c;

  public static HorarioDAO createDAO() throws SQLException  {
    if (dao == null) {
      dao = new HorarioDAO();
    }
    return dao;
  }

  private HorarioDAO() throws SQLException {
    c = DB.PQ().Conn();
  }

  public int LastID() throws SQLException {
    return DB.PQ().LastIDFrom("horario");
  }

  public ArrayList<HorarioDTO> SearchByDay(DayOfWeek day) throws SQLException {
    return getHorarioDTOs(queryByDay(day.toString()));
  }

  public ArrayList<HorarioDTO> SearchByMedicoID(int idMedico) throws SQLException {
    return getHorarioDTOs(queryByMedicoID(idMedico));
  }

  private String queryByDay(String day) throws SQLException {
    String query = String.format(
      "select * from horario where dia = '%s' order by hora_desde;", day
    );
    return query;
  }

  private String queryByMedicoID(int idMedico) throws SQLException {
    String query = String.format(
      "select * from horario where id_medico = %d order by dia, hora_desde;", idMedico
    );
    return query;
  }

  private ArrayList<HorarioDTO> getHorarioDTOs(String query) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    ArrayList<HorarioDTO> pacientes = new ArrayList<>();
    while (r.next()) {
      pacientes.add(
        new HorarioDTO(
          r.getInt("id"),
          r.getInt("id_medico"),
          DayOfWeek.of(r.getInt("dia")),
          LocalTime.parse(r.getString("hora_desde")),
          LocalTime.parse(r.getString("hora_hasta"))
        )
      );
    }
    r.close();
    s.close();
    return pacientes;
  }

  public HorarioDTO HorarioDTO(int id) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(
      "select * from horario where id = " + id
    );
    HorarioDTO dto = null;
    if (r.next()) {
      dto = new HorarioDTO(
          r.getInt("id"),
          r.getInt("id_medico"),
          DayOfWeek.of(r.getInt("dia")),
          LocalTime.parse(r.getString("hora_desde")),
          LocalTime.parse(r.getString("hora_hasta"))
      );
    }
    r.close();
    s.close();
    return dto; //si no existe devuelve null
  }

  public int Save(HorarioDTO h) throws SQLException {
    String query = String.format(
      "insert into horario values (default, %d, %d, '%s', '%s');",
      h.getIdMedico(), h.getDia().getValue(), h.getHoraDesde(), h.getHoraHasta()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return LastID();
  }

  public void DeleteByMedicoID(int id) throws SQLException {
    Statement s = c.createStatement();
    s.executeUpdate("delete from horario where id_medico = " + id);
    s.close();
  }

}
